﻿using Anke.SHManage.IDAL;
using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public partial class P_UserDAL : BaseDAL<P_User>, IP_UserDAL
    {
        IDALContext dalContext = new DALContextFactory().GetDALContext();
        AKV7ManageEntities db = DBContextFactory.GetDBContext() as AKV7ManageEntities;

        //取所有人员(combobox取值)
        public List<P_User> GetUserList()
        {

            string sql = @"select *  from  P_User  order by ID ";

            List<P_User> list = db.Database.SqlQuery<P_User>(sql).ToList();
            return list;
        }

        // 查询人员
        public object GetUserList(int page, int rows, ref int rowCounts, string strIsActive, string DiseaseID)
        {
            var q = (from m in db.P_User

                     select new
                     {
                         ID = m.ID,
                         DepID = m.DepID,
                         Name = m.Name,
                         IsActive = m.IsActive,
                         SN = m.SN,
                         LoginName = m.LoginName,
                         WorkCode = m.WorkCode,
                         Gender = m.Gender,
                         PassWord = m.PassWord
                     });


            if (!string.IsNullOrEmpty(strIsActive))
            {
                bool isActive = bool.Parse(strIsActive);
                q = q.Where(m => m.IsActive == isActive);
            }
            if (!string.IsNullOrEmpty(DiseaseID))
            {
                int diseaseID = int.Parse(DiseaseID);
                q = q.Where(m => m.ID == diseaseID);
            }

            rowCounts = q.Count();
            var r = q.OrderBy(u => u.SN).Skip((page - 1) * rows).Take(rows).ToList();
            return r;

        }
        // 查询人员
        public object GetUserList(int page, int rows, ref int rowCounts, string workID, string name
            , string searchBound, string selworkID, string depIDs)
        {
            List<P_User> list = new List<P_User>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
select distinct identity(int,1,1) as 行号,p.*,DepName=td.Name
into #temp  
from P_User p 
left join P_Department td on td.ID=p.DepID                                   
where 1=1 ");
            WhereClauseUtility.AddStringLike("p.WorkCode", workID, sb);
            WhereClauseUtility.AddStringLike("p.Name", name, sb);
            switch (searchBound)
            {
                case "all":
                    break;
                case "dep":
                    sb.Append(@" and p.DepID in (" + depIDs + ") ");
                    break;
                case "sel":
                    WhereClauseUtility.AddStringEqual("p.WorkCode", selworkID, sb);
                    break;
                default:
                    break;
            }
            sb.Append(@"
order by SN
select top " + rows + " A.*  from #temp A where 行号>" + (page - 1) * rows + @" order by 行号 
SELECT count(*) FROM #temp t
drop table #temp ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    P_User info = new P_User();
                    info.ID = Convert.ToInt32(dr["ID"]);
                    info.DepID = Convert.ToInt32(dr["DepID"]);
                    info.LoginName = dr["LoginName"].ToString();
                    //info.PassWord = dr["PassWord"].ToString(); //add2021-06-21 plq 三级等保测试高危--用户账户信息泄露---不返回任何用户个人敏感信息
                    info.WorkCode = dr["WorkCode"].ToString();
                    info.Name = dr["Name"].ToString();
                    info.Gender = dr["Gender"].ToString();
                    info.SN = Convert.ToInt32(dr["SN"]);
                    info.IsActive = Convert.ToBoolean(dr["IsActive"]);
                    info.DepName = dr["DepName"].ToString();
                    info.PhysicianCode = dr["PhysicianCode"].ToString(); //add2021-10-15 plq 配合医保上线移植
                    info.YBCountryCode = dr["YBCountryCode"].ToString(); //add2022-03-28 plq 配合医保国家码上线移植
                    list.Add(info);
                }
            }
            rowCounts = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            var result = new { total = rowCounts, rows = list };
            return result;
        }
        /// <summary>
        /// 测试更新  
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool TestTransaction(P_User user)
        {
            using (var tran = db.Database.BeginTransaction())
            {
                try
                {

                    P_User temp = dalContext.IP_UserDAL.GetModelWithOutTrace(p => p.ID == 5);
                    temp.SN = 222;

                    P_User u = new P_User();
                    u.ID = temp.ID;
                    u.SN = 222;

                    dalContext.IP_UserDAL.Modify(u, "SN");

                    tran.Commit(); //提交事务

                    return true;
                }
                catch (Exception)
                {
                    if (tran != null)
                        tran.Rollback();  //回滚事务

                    return false;
                }
            }
        }

        /// <summary>
        /// 获取带教人员下拉字典 （combobox用）   
        /// add2024-08-29 plq
        /// </summary>
        /// <returns></returns>
        public object GetTeachUserDic()
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select WorkCode,Name from P_User where IsActive = 1");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["WorkCode"].ToString();
                    info.Name = dr["Name"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取人员的带教医生数据(闵行移植参考)
        /// add2024-08-29 plq
        /// </summary>
        /// <param name="workcode"></param>
        /// <returns></returns>
        public object GetUserGroup(string workcode)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select pug.PWorkCode from P_UserGroup pug where WorkCode='" + workcode + "'");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                List<P_User> list = new List<P_User>();
                if (ds.Tables[0].Rows.Count > 0)
                {
                    P_User info = new P_User();
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        info = new P_User();
                        info.WorkCode = dr["PWorkCode"].ToString();
                        list.Add(info);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("P_UserDALExt/GetUserGroup", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 保存人员的带教医生数据(闵行移植参考)
        /// add2024-08-29 plq 
        /// </summary>
        /// <param name="workcode"></param>
        /// <returns></returns>
        public bool SaveUserGroup(string workcode, string pworkcode)
        {
            StringBuilder sb = new StringBuilder();
            try
            {
                sb.Append(@"select * from P_UserGroup where WorkCode = '" + workcode + "'");
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                StringBuilder sql2 = new StringBuilder();
                if (ds.Tables[0].Rows.Count > 0)
                {
                    sql2.Append(@"update P_UserGroup set PWorkCode='" + pworkcode + "' where WorkCode ='" + workcode + "'");
                    int num = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sql2.ToString(), null);
                    if (num > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    //if (!string.IsNullOrEmpty(pworkcode) && pworkcode.Trim() != "")
                    //{
                    sql2.Append(@"insert into P_UserGroup (WorkCode,PWorkCode) values('" + workcode + "','" + pworkcode + "')");
                    int num = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sql2.ToString(), null);
                    if (num > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                    //}
                    //else
                    //{
                    //    return true;
                    //}
                }
            }
            catch (Exception ex)
            {
                LogUtility.Error("P_UserDALExt/SaveUserGroup", ex.Message);
                return false;
            }
        }


    }
}
